<?php

class DB {
    private static $instance = null;
    private $pdo;
    private $table;
    private $wheres = [];
    private $bindings = [];
    private $orders = [];
    private $joins = [];
    private $limit;
    private $offset;
    private $columns = [];
    private $excludeFields = ['id', 'created_at', 'updated_at'];
    private $cache = [];
    private $cacheTtl = 300;
    // 添加事务层级计数器
    private $transactionLevel = 0;
    
    // 构造函数改为私有，确保只能通过instance创建
    private function __construct($dsn, $config) {
        $this->pdo = new PDO($dsn, $config['username'], $config['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
    }

    public static function instance($dsn = null, $config = null) {
        if (!self::$instance) {
            if (!$dsn || !$config) {
                throw new RuntimeException("首次初始化必须提供DSN和配置参数");
            }
            self::$instance = new self($dsn, $config);
        }
        return self::$instance;
    }
    
    public static function table($table) {
        $instance = self::instance();
        $instance->table = $table;
        $instance->reset();
        return $instance;
    }

    public function where($column, $operator = null, $value = null, $boolean = 'AND') {
        if ($column instanceof Closure) {
            return $this->whereNested($column, $boolean);
        }
        if (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }
        $this->validateColumn($column);
        $this->wheres[] = [
            'type' => 'basic',
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => $boolean
        ];
        $this->bindings[] = $value;
        return $this;
    }

    public function orWhere($column, $operator = null, $value = null) {
        if ($column instanceof Closure) {
            return $this->whereNested($column, 'OR');
        }

        if (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }

        return $this->where($column, $operator, $value, 'OR');
    }

    protected function whereNested(Closure $callback, $boolean = 'AND') {
        $query = clone $this;
        $query->resetForNested();
        call_user_func($callback, $query);
        $this->wheres[] = [
            'type' => 'nested',
            'query' => $query,
            'boolean' => $boolean
        ];
        $this->bindings = array_merge($this->bindings, $query->bindings);
        return $this;
    }

    private function resetForNested() {
        $this->wheres = [];
        $this->bindings = [];
        $this->orders = [];
        $this->joins = [];
        $this->limit = null;
        $this->offset = null;
        $this->columns = [];
    }

    public function orderBy($column, $direction = 'ASC') {
        $this->validateColumn($column);
        $direction = strtoupper($direction) === 'ASC' ? 'ASC' : 'DESC';
        $this->orders[] = "`$column` $direction";
        return $this;
    }

    public function join($table, $first, $operator, $second, $type = 'INNER') {
        $this->validateColumn($table);
        $this->validateColumn($first);
        $this->validateColumn($second);
        $this->joins[] = "$type JOIN `$table` ON `$first` $operator `$second`";
        return $this;
    }

    public function select($columns) {
        $this->columns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }

    public function insert(array $data) {
        $columns = array_map(function($col) {
            return "`$col`";
        }, array_keys($data));

        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO `{$this->table}` (".implode(', ', $columns).") VALUES ($placeholders)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_values($data));
        return $this->pdo->lastInsertId();
    }

    public function insertBatch(array $rows) {
        if (empty($rows)) return 0;
        
        $columns = array_keys($rows[0]);
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        $sql = "INSERT INTO `{$this->table}` (`".implode('`,`', $columns)."`) 
                VALUES ".implode(', ', array_fill(0, count($rows), "($placeholders)"));
        
        $bindings = [];
        foreach ($rows as $row) {
            $bindings = array_merge($bindings, array_values($row));
        }
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($bindings);
    }

    public function update(array $data) {
        $filteredData = array_diff_key($data, array_flip($this->excludeFields));
        $setClause = [];
        $bindings = [];
        foreach ($filteredData as $key => $value) {
            $setClause[] = "`$key` = ?";
            $bindings[] = $value;
        }
        $sql = "UPDATE `{$this->table}` SET ".implode(', ', $setClause);
        $sql .= $this->buildWhere();
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(array_merge($bindings, $this->bindings));
    }

    public function delete() {
        $sql = "DELETE FROM `{$this->table}`";
        $sql .= $this->buildWhere();
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($this->bindings);
    }

    public function get() {
        $sql = $this->buildSelect();
        $sql .= " FROM `{$this->table}`";
        $sql .= $this->buildJoin();
        $sql .= $this->buildWhere();
        $sql .= $this->buildOrderBy();
        $sql .= $this->buildLimit();
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        return $stmt->fetchAll();
    }

    public function first() {
        return $this->limit(1)->get()[0] ?? null;
    }

    public function paginate($perPage = 15, $page = 1) {
        $total = $this->count();
        $results = $this->limit($perPage)->offset(($page - 1) * $perPage)->get();
        return [
            'data' => $results,
            'total' => $total,
            'per_page' => $perPage,
            'current_page' => $page,
            'last_page' => ceil($total / $perPage)
        ];
    }

    public function count($column = '*') {
        $this->validateColumn($column);
        $result = $this->select("COUNT(`$column`) as aggregate")->first();
        return (int) ($result['aggregate'] ?? 0);
    }
    
    public function limit($limit) {
        $this->limit = (int) $limit; // 设置限制数量
        return $this;
    }

    public function beginTransaction(): void {
        if ($this->transactionLevel === 0) {
            $this->pdo->beginTransaction();
        } else {
            $this->pdo->exec("SAVEPOINT LEVEL{$this->transactionLevel}");
        }
        $this->transactionLevel++;
    }

    public function commit(): void {
        if ($this->transactionLevel > 0) {
            $this->transactionLevel--;
            if ($this->transactionLevel === 0) {
                $this->pdo->commit();
            } else {
                $this->pdo->exec("RELEASE SAVEPOINT LEVEL{$this->transactionLevel}");
            }
        }
    }

    public function rollBack(): void {
        if ($this->transactionLevel > 0) {
            $this->transactionLevel = 0; // 回滚所有嵌套层级
            $this->pdo->rollBack();
        }
    }

    public function inTransaction(): bool {
        return $this->pdo->inTransaction();
    }

    private function buildSelect() {
        if (empty($this->columns)) {
            return 'SELECT *';
        }

        $columns = [];
        foreach ($this->columns as $col) {
            if (preg_match('/\(.*\)/', $col)) {
                $columns[] = $col;
            } else {
                $columns[] = "`$col`";
            }
        }
        return 'SELECT '.implode(', ', $columns);
    }

    private function buildJoin() {
        return $this->joins ? ' '.implode(' ', $this->joins) : '';
    }

    private function buildWhere() {
        $clauses = [];
        foreach ($this->wheres as $where) {
            if ($where['type'] === 'basic') {
                $clauses[] = [
                    'sql' => "`{$where['column']}` {$where['operator']} ?",
                    'boolean' => $where['boolean']
                ];
            } elseif ($where['type'] === 'nested') {
                $nestedSql = $where['query']->buildWhere();
                $clauses[] = [
                    'sql' => "(".substr(trim($nestedSql), 6).")", // Remove WHERE keyword
                    'boolean' => $where['boolean']
                ];
            }
        }
        $sql = '';
        foreach ($clauses as $index => $clause) {
            if ($index === 0) {
                $sql .= $clause['sql'];
            } else {
                $sql .= " {$clause['boolean']} {$clause['sql']}";
            }
        }
        return $sql ? " WHERE $sql" : '';
    }

    private function buildOrderBy() {
        return $this->orders ? ' ORDER BY '.implode(', ', $this->orders) : '';
    }

    private function buildLimit() {
        if (!$this->limit) return '';
        $sql = " LIMIT {$this->limit}";
        if ($this->offset) {
            $sql .= " OFFSET {$this->offset}";
        }
        return $sql;
    }

    private function validateColumn($column) {
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_\.]*$/', $column)) {
            throw new InvalidArgumentException("非法字段名: $column");
        }
    }

    public function excludeFields(array $fields) {
        $this->excludeFields = $fields;
        return $this;
    }

    private function reset() {
        $this->wheres = [];
        $this->bindings = [];
        $this->orders = [];
        $this->joins = [];
        $this->limit = null;
        $this->offset = null;
        $this->columns = [];
        $this->excludeFields = ['id', 'created_at', 'updated_at'];
    }
}

// 使用方法
// 获取实例：DB::instance();

// // 事务处理
// DB::beginTransaction();
// try {
//     DB::table('users')->insert(...);
//     DB::table('logs')->insert(...);
//     DB::commit();
// } catch (Exception $e) {
//     DB::rollBack();
// }

// // JOIN查询
// DB::table('orders')
//     ->join('users', 'orders.user_id', '=', 'users.id')
//     ->select('orders.*', 'users.name')
//     ->get();

// // 分页查询
// $result = DB::table('products')
//     ->orderBy('price', 'DESC')
//     ->paginate(20, 2);

// // 聚合查询
// $total = DB::table('orders')->count();

// // 获取数据
// $data = DB::table('users')->where('id', 1)->first();

// 复合条件处理：
// DB::table('articles')
//     ->where('status', 'published')
//     ->where(function($query) {
//         $query->where('views', '>', 1000)
//               ->orWhere('is_featured', 1);
//     })->get();

// // 修改数据
// $data['name'] = '张三';
// $data['updated_at'] = date('Y-m-d H:i:s'); // 此字段会被自动排除

// // 执行更新（不会修改id/created_at等字段）
// DB::table('users')->where('id', 1)->update($data);

// OR条件查询
// $results = DB::table('products')->where('category', '电子产品')->orWhere('price', '>', 5000)->get();


// 插入数据
// $newId = DB::table('users')->insert([
//     'name' => '李四',
//     'email' => 'lisi@example.com',
//     'created_at' => date('Y-m-d H:i:s')
// ]);